/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package air.sb;

import air.eb.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.sql.DataSource;

/**
 *
 * @author Keith
 */
@Stateless
public class LuftBean implements LuftRemote {

    @Resource(name = "airdba")
    private DataSource airdba;
    private Connection c;
    private Collection<Airport> airports;
    private ArrayList<ArrayList<Flightinstance>> routes;
    private Statement s;
    private PreparedStatement ps;
    private String receipt;
    private ArrayList<Flightinstance> depFlights;
    private ArrayList<Flightinstance> arrFlights;
    private Collection<Seatinstance> seats = null;
    private Collection<Flightinstance> route = null;
    private int nrPassenger;
    private int tripNr;
    private String arr, dep;
    private int currentTripNr;


    public ArrayList<ArrayList<Flightinstance>> getRoutes() {
        return routes;
    }

    public String getReceipt(int tripNr) {
        receipt = null;
        int i = 0, k = 0;
        try {
            c = airdba.getConnection();
            s = c.createStatement();
            ResultSet r = s.executeQuery("SELECT * FROM Trips WHERE nr = " + tripNr);
            c.close();
            if (r.next()) {
                receipt = "Trip Number: " + r.getInt("nr");
                receipt += "<br/> Head: " + r.getString("passenger") + "<br/>";

                //gets all passengers
                c = airdba.getConnection();
                s = c.createStatement();
                ResultSet rs1 = s.executeQuery("SELECT * FROM GroupBooking WHERE leader = '" + r.getString("passenger") + "'");
                c.close();
                while (rs1.next()) {
                    k++;
                    receipt += "<br/> Member " + k + ": " + rs1.getString("member");
                }

                //gets flight info
                receipt += "<br/>";
                c = airdba.getConnection();
                s = c.createStatement();
                ResultSet rs2 = s.executeQuery("SELECT * FROM ConsistsOf WHERE tripNr = " + tripNr);
                c.close();
                while (rs2.next()) {
                    i++;
                    receipt += "<br/> Flight " + i + ": " + rs2.getInt("flight");
                    receipt += "<br/> Departure: " + rs2.getString("depDate");
                    receipt += "<br/> Arrival: " + rs2.getString("arrDate");
                }

            }

        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        return receipt;
    }

    public Collection<Flightinstance> getRoute() {
        return route;
    }

    public Collection<Seatinstance> getFreeSeats() {
        return seats;
    }

    public Collection<Airport> getAllAirports() {
        return airports;
    }

    public void findRoutes(String d, String dep, String dest, int nrPassenger) {
        ArrayList<ArrayList<Flightinstance>> res = new ArrayList<ArrayList<Flightinstance>>();
        getArrivalFlights(dest, nrPassenger);
        try {
            c = airdba.getConnection();
            ps = c.prepareStatement("SELECT * FROM FlightInstances fi WHERE" +
                    " fi.depDate = '" + d + "' AND fi.depAirport = '" + dep +
                    "' AND fi.arrAirport = '" + dest + "' AND fi.availableSeats >= " + nrPassenger);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ArrayList r = new ArrayList<Flightinstance>();
                r.add(new Flightinstance(rs.getString("nr"), rs.getDouble("fare"),
                        rs.getString("depTime"), rs.getString("arrTime"),
                        rs.getString("airline"), rs.getString("depAirport"),
                        rs.getString("arrAirport"), rs.getString("depDate"),
                        rs.getString("arrDate"), rs.getInt("availableSeats")));
                res.add(r);
            }
            this.nrPassenger = nrPassenger;
            c.close();
        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (!res.isEmpty()) {
            routes = res;
        }
    }

    private ArrayList<Flightinstance> getArrivalFlights(String arr, int nrPassenger) {
        System.out.println("YES KRISTIAN");

        return null;
    }

    public void findFlightSeats(List<String> flights) {
        ArrayList<Seatinstance> res = new ArrayList<Seatinstance>();

        for (String flight : flights) {
            try {
                c = airdba.getConnection();
                ps = c.prepareStatement("SELECT * FROM SeatInstances s WHERE s.flight = '" + flight + "' AND s.empty = 1");
                ResultSet rs = ps.executeQuery();
                c.close();
                while (rs.next()) {
                    Seatinstance si = new Seatinstance(rs.getInt("nr"), rs.getString("sRow"),
                            rs.getString("flight"), rs.getBoolean("empty"), rs.getString("arrDate"), rs.getString("depDate"));
                    res.add(si);
                }
            } catch (SQLException ex) {
                Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (!res.isEmpty()) {
            seats = res;
        }
    }

    public void findTrip(int tripNumber, String pNumber) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public ArrayList<ArrayList<Flightinstance>> getValidFlights() {
        return routes;
    }

    public void findTripToBeDeleted(List<Integer> sNr, List<String> sRow, List<String> name, List<String> pNr,
            List<String> arrDate, List<String> depDate, List<String> flights, int nrOfFlights) {
        String temp = "h";
        //return temp;
    }

    public void findAllAirports() {
        Collection<Airport> res = new ArrayList<Airport>();
        try {
            c = airdba.getConnection();
            ps = c.prepareStatement("SELECT * FROM Airports");
            ResultSet rs = ps.executeQuery();
            c.close();
            while (rs.next()) {
                res.add(new Airport(rs.getString("code"), rs.getString("name"), rs.getString("city"), rs.getString("country")));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        airports = res;
    }

    public boolean getDelLogin(int tripNr, String pNr) {
        //throw new UnsupportedOperationException("Not supported yet.");
        boolean result = false;
        ResultSet rs = null;
        try {
            c = airdba.getConnection();
            s = c.createStatement();
            rs = s.executeQuery("SELECT nr,passenger FROM Trips tr WHERE tr.nr= " + tripNr + " AND tr.passenger = '" + pNr + "'");
            result = rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
                }
                rs = null;
            }
            if (s != null) {
                try {
                    s.close();
                } catch (SQLException ex) {
                    Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
                }
                s = null;
            }
            if (c != null) {
                try {
                    c.close();
                } catch (SQLException ex) {
                    Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
                }
                c = null;
            }
        }
        return result;
    }

    public Flightinstance getFlightinstanceByPK(String pk) {
        Flightinstance fi = null;
        try {
            c = airdba.getConnection();
            ps = c.prepareStatement("SELECT * FROM FlightInstances fi WHERE fi.nr = '" + pk + "'");
            ResultSet rs = ps.executeQuery();
            c.close();
            if (rs.next()) {
                fi = new Flightinstance(rs.getString("nr"), rs.getDouble("fare"), rs.getString("depTime"), rs.getString("arrTime"), rs.getString("airline"), rs.getString("depAirport"), rs.getString("arrAirport"), rs.getString("depDate"), rs.getString("arrDate"), rs.getInt("availableseats"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        return fi;
    }

    public void bookTrip(ArrayList<Integer> sNr, ArrayList<String> sRow, ArrayList<String> name, ArrayList<String> pNr, ArrayList<String> arrDate, ArrayList<String> depDate, ArrayList<String> flights, ArrayList<String> airlines, int nrOfFlights, int tripNr) {
        try {
            //Variables
            String leader = pNr.get(0);
            currentTripNr = tripNr;
            //calculating number of passengers
            int nrOfPass = pNr.size() / nrOfFlights;


            //Creating the Leader
            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("INSERT INTO Passengers VALUES ('" + pNr.get(0) + "', '" + name.get(0) + "', " + sNr.get(0) + ", '" + sRow.get(0) + "', '" + flights.get(0) + "')");
            c.close();

            //Creating Trip
            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("INSERT INTO Trips VALUES(" + tripNr + ", '" + pNr.get(0) + "', '" + flights.get(0) + "')");
            c.close();

            //Creating Group Booking
            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("INSERT INTO GroupBooking VALUES ('" + pNr.get(0) + "',  '" + pNr.get(0) + "',  '" + flights.get(0) + "')");
            c.close();

            //Creating Consists Of
            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("INSERT INTO ConsistsOf VALUES ('" + flights.get(0) + "', '" + arrDate.get(0) + "', '" + depDate.get(0) + "', " + tripNr + ")");
            c.close();

            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("UPDATE SeatInstances SET empty = 0 WHERE flight = '" + flights.get(0) + "' AND nr = " + sNr.get(0) + " AND srow = '" + sRow.get(0) + "'");
            c.close();

            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("UPDATE FlightInstances SET availableSeats = availableSeats - " + nrOfPass + " WHERE nr = '" + flights.get(0) + "'");
            c.close();

            //Creating and uppdating for all other cases
            for (int i = 1; i < flights.size(); i++) {

                //If Leader (=for each flight)
                if ((i % nrOfPass) == 0 && (airlines.get(i).equalsIgnoreCase(airlines.get(0)))) {
                    //Passenger
                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("INSERT INTO Passengers VALUES ('" + pNr.get(i) + "', '" + name.get(i) + "', " + sNr.get(i) + ", '" + sRow.get(i) + "', '" + flights.get(i) + "')");
                    c.close();

                    //Group Booking
                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("INSERT INTO GroupBooking VALUES ('" + pNr.get(i) + "',  '" + pNr.get(i) + "',  '" + flights.get(i) + "')");
                    c.close();

                    //Consists Of
                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("INSERT INTO ConsistsOf VALUES ('" + flights.get(i) + "', '" + arrDate.get(i) + "', '" + depDate.get(i) + "', " + tripNr + ")");
                    c.close();

                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("UPDATE SeatInstances SET empty = 0 WHERE flight = '" + flights.get(i) + "' AND nr = " + sNr.get(i) + " AND srow = '" + sRow.get(i) + "'");
                    c.close();

                    //Flight Instance
                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("UPDATE FlightInstances SET availableSeats = availableSeats - " + nrOfPass + " WHERE nr = '" + flights.get(0) + "'");
                    c.close();

                } else if ((i % nrOfPass) == 0 && !(airlines.get(i).equalsIgnoreCase(airlines.get(0)))) {
                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("INSERT INTO CanBe VALUES (" + tripNr + ",  '" + airlines.get(i) + "', '" + flights.get(i) + "')");
                    c.close();

                } //Just a member, not Leader
                else if (airlines.get(i).equalsIgnoreCase(airlines.get(0))) {
                    //Passenger
                    c = airdba.getConnection();

                    s = c.createStatement();
                    s.executeUpdate("INSERT INTO Passengers VALUES ('" + pNr.get(i) + "', '" + name.get(i) + "', " + sNr.get(i) + ", '" + sRow.get(i) + "', '" + flights.get(i) + "')");
                    c.close();

                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("INSERT INTO GroupBooking VALUES ('" + leader + "',  '" + pNr.get(i) + "',  '" + flights.get(i) + "')");
                    c.close();

                    //Seat Instance
                    c = airdba.getConnection();
                    s = c.createStatement();
                    s.executeUpdate("UPDATE SeatInstances SET empty = 0 WHERE flight = '" + flights.get(i) + "' AND nr = " + sNr.get(i) + " AND srow = '" + sRow.get(i) + "'");
                    c.close();
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void bookOther(int sNr, String sRow, String name, String pNr, String flights) {
        try {
            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("INSERT INTO Passengers VALUES ('" + pNr + "', '" + name + "', " + sNr + ", '" + sRow + "', '" + flights + "')");
            c.close();

            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("UPDATE SeatInstances SET empty = 0 WHERE flight = '" + flights + "' AND nr = " + sNr + " AND srow = '" + sRow + "'");
            c.close();

            c = airdba.getConnection();
            s = c.createStatement();
            s.executeUpdate("UPDATE FlightInstances SET availableSeats = availableSeats - " + 1 + " WHERE nr = '" + flights + "'");
            c.close();
        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int getNumberOfPassengers() {
        return nrPassenger;
    }

    public void setRoute(Collection<Flightinstance> route) {
        this.route = route;
    }

    public void deleteTrip(int tripNr, String pNr) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void findArrivalFlights(String arr, int nrPassenger) {
        this.arr = arr;
        ArrayList r = new ArrayList<Flightinstance>();
        try {
            c = airdba.getConnection();
            ps = c.prepareStatement("SELECT * FROM FlightInstances fi WHERE fi.availableSeats >= " + nrPassenger);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {

                r.add(new Flightinstance(rs.getString("nr"), rs.getDouble("fare"),
                        rs.getString("depTime"), rs.getString("arrTime"),
                        rs.getString("airline"), rs.getString("depAirport"),
                        rs.getString("arrAirport"), rs.getString("depDate"),
                        rs.getString("arrDate"), rs.getInt("availableSeats")));
            }
            c.close();
        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        arrFlights = r;
    }

    public void findDepartureFlights(String dep, int nrPassenger) {
        this.dep = dep;
        ArrayList<Flightinstance> re = new ArrayList<Flightinstance>();
        try {
            c = airdba.getConnection();
            ps = c.prepareStatement("SELECT * FROM FlightInstances fi WHERE fi.availableSeats >= " + nrPassenger);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {

                re.add(new Flightinstance(rs.getString("nr"), rs.getDouble("fare"),
                        rs.getString("depTime"), rs.getString("arrTime"),
                        rs.getString("airline"), rs.getString("depAirport"),
                        rs.getString("arrAirport"), rs.getString("depDate"),
                        rs.getString("arrDate"), rs.getInt("availableSeats")));
            }
            c.close();
        } catch (SQLException ex) {
            Logger.getLogger(AirBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        depFlights = re;
    }

    public ArrayList<Flightinstance> getArrivalFlights() {
        return arrFlights;
    }

    public ArrayList<Flightinstance> getDepartureFlights() {
        return depFlights;
    }

    public int getNrTrips() {
        try{
            c = airdba.getConnection();
            s = c.createStatement();
            ResultSet rs = s.executeQuery("SELECT * FROM Trips");
            c.close();
            int res = 0;
            while(rs.next()){
                res++;
            }
            tripNr = res;
        }catch(SQLException sx){

        }
        return tripNr;
    }

    public String getDep() {
        return dep;
    }

    public String getArr() {
        return arr;
    }

    public int getTripNr(){
        return currentTripNr;
    }
}
